<?php

/**
 * Objeto para a contrução de comandos sql.
 * 
 * @author Bruno E. Fuhr [brunofuhr@gmail.com]
 * @since 06/03/2013
 */
class FSQL
{
    
    private $sql;
    
    private $where;
    
    private $tables;
    
    private $columns;
    
    private $leftJoins;
    
    private $rightJoins;
    
    private $innerJoins;
    
    private $parameters;
    
    private $orderBy;
    
    private $limit;
    
    private $offset;
    
    const NULL_VALUE = '__NVL__';
    
    public function select()
    {
        $this->sql = ' SELECT ';
        
        $this->sql .= $this->parseColumns() . ' FROM ';
        $this->sql .= $this->parseTables();
        
        $innerJoin = $this->parseInnerJoins();
        if ( strlen($innerJoin) > 0 )
        {
            $this->sql .= ' INNER JOIN ' . $innerJoin;
        }
        
        $leftJoin = $this->parseLeftJoins();
        if ( strlen($leftJoin) > 0 )
        {
            $this->sql .= ' LEFT JOIN ' . $leftJoin;
        }
        
        $where = $this->parseWhere();
        if ( strlen($where) > 0 )
        {
            $this->sql .= ' WHERE ' . $where;
        }
        
        if ( strlen($this->orderBy) > 0 )
        {
            $this->sql .= ' ORDER BY ' . $this->orderBy;
        }
        
        if ( strlen($this->limit) > 0 )
        {
            $this->sql .= ' LIMIT ' . $this->limit;
            
            if ( strlen($this->offset) > 0 )
            {
                $this->sql .= ' OFFSET ' . $this->offset;
            }
        }
        
        return $this->sql;
    }
    
    public function insert()
    {
        $this->sql = ' INSERT INTO ' . $this->parseTables() . ' ( ' . $this->parseColumns() . ' ) VALUES ( ' . "'{$this->parseParameters()}'" . ' )';
        return $this->sql;
    }
    
    public function update()
    {
        $this->sql = ' UPDATE ' . $this->parseTables() . ' SET ';
        foreach ( $this->columns as $key => $column )
        {
            if ( $this->parameters[$key] == FSQL::NULL_VALUE )
            {
                $this->sql .= $column . " = NULL,";
            }
            else
            {
                $this->sql .= $column . " = '{$this->parameters[$key]}',";
            }
        }
        
        // Remover a última vírgula.
        $this->sql = substr($this->sql, 0, strlen($this->sql) - 1);
        
        $where = $this->parseWhere();
        if ( strlen($where) > 0 )
        {
            $this->sql .= ' WHERE ' . $where;
        }
        
        return $this->sql;        
    }
    
    public function delete()
    {
        $this->sql = ' DELETE FROM ' . $this->parseTables();
        
        $where = $this->parseWhere();
        if ( strlen($where) > 0 )
        {
            $this->sql .= ' WHERE ' . $where;
        }
        
        return $this->sql;
    }
    
    private function parseColumns()
    {
        if ( !$this->columns )
        {
            $this->addColumn('*');
        }
        return implode(',', $this->columns);
    }
    
    private function parseTables()
    {
        $tables = '';
        
        foreach ( $this->tables as $alias => $tableName )
        {
            if ( is_numeric($alias) )
            {
                $tables .= " $tableName,";
            }
            else
            {
                $tables .= " $tableName $alias,";
            }
        }
        
        return substr($tables, 0, strlen($tables) - 1);
    }
    
    private function parseWhere()
    {
        $where = '';
        $whereAnd = '';
        $whereOr = '';
        
        if ( count($this->where['AND']) > 0 )
        {
            $whereAnd = implode(' AND ', $this->where['AND']);
        }
        
        if ( count($this->where['OR']) > 0 )
        {
            $whereOr = implode(' OR ', $this->where['OR']);
        }
        
        if ( strlen($whereAnd) > 0 && strlen($whereOr) > 0 )
        {
            $where .= $whereAnd . ' OR ' . $whereOr;
        }
        elseif ( strlen($whereAnd) > 0 )
        {
            $where .= $whereAnd;
        }
        elseif ( strlen($whereOr) > 0)
        {
            $where .= $whereOr;
        }
        
        foreach( $this->where['PARAMS'] as $parameter )
        {
            $where = preg_replace('/\?/', $parameter, $where, 1);
        }
        
        return $where;        
    }
    
    private function parseInnerJoins()
    {
        return implode(' INNER JOIN ', $this->innerJoins);
    }
    
    private function parseLeftJoins()
    {
        return implode(' LEFT JOIN ', $this->leftJoins);
    }
    
    private function parseParameters()
    {
        return implode("','", $this->parameters);
    }

    public function getSql() {
        return $this->sql;
    }

    public function setSql($sql) {
        $this->sql = $sql;
    }

    public function getWhere() {
        return $this->where;
    }

    public function setWhere($where, $parameters = null)
    {
        $this->setWhereAnd($where, $parameters);
    }
    
    public function setWhereAnd($where, $parameters)
    {
        $this->where['AND'][] = $where;
        if ( $parameters != null )
        {
            if ( is_array($parameters) )
            {
                foreach( $parameters as $param )
                {
                    $this->where['PARAMS'][] = $param;
                }
            }
            else
            {
                $this->where['PARAMS'][] = $parameters;
            }
        }
    }
    
    public function setWhereOr($where, $parameters)
    {
        $this->where['OR'][] = $where;
        if ( $parameters != null )
        {
            if ( is_array($parameters) )
            {
                foreach( $parameters as $param )
                {
                    $this->where['PARAMS'][] = $param;
                }
            }
            else
            {
                $this->where['PARAMS'][] = $parameters;
            }
        }
    }

    public function getTables() {
        return $this->tables;
    }
    
    public function addTable($name, $alias=null)
    {
        if ( $alias )
        {
            $this->tables[$alias] = $name;
        }
        else
        {
            $this->tables[] = $name;
        }
    }

    public function getColumns() {
        return $this->columns;
    }
    
    public function setColumns($columns)
    {
        if (is_array($columns) )
        {
            foreach($columns as $column)
            {
                $this->addColumn($column);
            }
        }
    }
    
    public function addColumn($name)
    {
        $this->columns[] = $name;
    }

    public function getLeftJoins() {
        return $this->leftJoins;
    }
    
    public function addLeftJoin($table, $on)
    {
        $this->leftJoins[] = $table . ' ON ( ' . $on . ' ) ';
    }

    public function getRightJoins() {
        return $this->rightJoins;
    }
    
    public function addRightJoin($table, $on)
    {
        $this->rightJoins[] = $table . ' ON ( ' . $on . ' ) ';
    }

    public function getInnerJoins() {
        return $this->innerJoins;
    }
    
    public function addInnerJoin($table, $on)
    {
        $this->innerJoins[] = $table . ' ON ( ' . $on . ' ) ';
    }
    
    public function getParameters() {
        return $this->parameters;
    }

    public function setParameters($parameters)
    {
        if ( is_array($parameters) )
        {
            foreach ($parameters as $parameter )
            {
                $this->addParameter($parameter);
            }
        }
        else
        {
            $this->addParameter($parameters);
        }
    }
    
    public function addParameter($parameter)
    {
        if ( is_array($parameter) )
        {
            foreach ($parameter as $p )
            {
                $this->addParameter($p);
            }
        }
        else
        {
            $this->parameters[] = $parameter;
        }
    }
    
    public function getOrderBy() {
        return $this->orderBy;
    }

    public function setOrderBy($orderBy) {
        $this->orderBy = $orderBy;
    }
    
    public function getLimit() {
        return $this->limit;
    }

    public function setLimit($limit)
    {
        $this->limit = $limit;
    }

    public function getOffset() {
        return $this->offset;
    }

    public function setOffset($offset)
    {
        $this->offset = $offset;
    }

}

?>
